﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ManagerDA
/// </summary>
public class ManagerDA
{
    private DataAccess da;

	public ManagerDA()
	{
        da= new DataAccess();
		//
		// TODO: Add constructor logic here
		//
	}
    public DataSet managerLogin(string username, string pass)
    {
        string query = "select * from Manager where userName=@username and pass=@pass";
        SqlParameter[] sqlParameters = new SqlParameter[2];
        sqlParameters[0] = new SqlParameter("@username", SqlDbType.VarChar);
        sqlParameters[0].Value = Convert.ToString(username);
        sqlParameters[1] = new SqlParameter("@pass", SqlDbType.VarChar);
        sqlParameters[1].Value = Convert.ToString(pass);
        DataSet ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }
    public bool insertStudent(Student s)
    {
        int studentId = s.Id;
        string userName = s.Username;
        string pass = s.Password;
        string fullName = s.Fullname;
        string address = s.Address;
        string email = s.Email;
        string phoneNo = s.PhoneNumber;
        string major = s.Major;
        string _class = s._class1;

        string query = "insert into Student values(@userName,@pass,@fullName,@address,@email,@phoneNo,@major,@class)";

        SqlParameter[] sqlParameters = new SqlParameter[8];
        sqlParameters[0] = new SqlParameter("@userName", SqlDbType.NVarChar);
        sqlParameters[0].Value = Convert.ToString(userName);
        sqlParameters[1] = new SqlParameter("@pass", SqlDbType.NVarChar);
        sqlParameters[1].Value = Convert.ToString(pass);
        sqlParameters[2] = new SqlParameter("@fullName", SqlDbType.NVarChar);
        sqlParameters[2].Value = Convert.ToString(fullName);
        sqlParameters[3] = new SqlParameter("@address", SqlDbType.NVarChar);
        sqlParameters[3].Value = Convert.ToString(address);
        sqlParameters[4] = new SqlParameter("@email", SqlDbType.NVarChar);
        sqlParameters[4].Value = Convert.ToString(email);
        sqlParameters[5] = new SqlParameter("@phoneNo", SqlDbType.NVarChar);
        sqlParameters[5].Value = Convert.ToString(phoneNo);
        sqlParameters[6] = new SqlParameter("@major", SqlDbType.NVarChar);
        sqlParameters[6].Value = Convert.ToString(major);
        sqlParameters[7] = new SqlParameter("@class", SqlDbType.NVarChar);
        sqlParameters[7].Value = Convert.ToString(_class);

        return da.executeInsertQuery(query, sqlParameters);
    }
    public bool updateStudent(Student s)
    {
        int studentId = s.Id;
        string userName = s.Username;
        string fullName = s.Fullname;
        string address = s.Address;
        string email = s.Email;
        string phoneNo = s.PhoneNumber;
        string major = s.Major;
        string _class = s._class1;
        string username = s.Username;

        string query = "update Student set userName=@username,fullName=@fullName,address=@address,email=@email,phoneNo=@phoneNo,major=@major,class=@class where studentId=@studentId";

        SqlParameter[] sqlParameters = new SqlParameter[8];
        sqlParameters[0] = new SqlParameter("@studentId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(studentId);
        sqlParameters[1] = new SqlParameter("@fullName", SqlDbType.NVarChar);
        sqlParameters[1].Value = Convert.ToString(fullName);
        sqlParameters[2] = new SqlParameter("@address", SqlDbType.NVarChar);
        sqlParameters[2].Value = Convert.ToString(address);
        sqlParameters[3] = new SqlParameter("@email", SqlDbType.NVarChar);
        sqlParameters[3].Value = Convert.ToString(email);
        sqlParameters[4] = new SqlParameter("@phoneNo", SqlDbType.NVarChar);
        sqlParameters[4].Value = Convert.ToString(phoneNo);
        sqlParameters[5] = new SqlParameter("@major", SqlDbType.NVarChar);
        sqlParameters[5].Value = Convert.ToString(major);
        sqlParameters[6] = new SqlParameter("@class", SqlDbType.NVarChar);
        sqlParameters[6].Value = Convert.ToString(_class);
        sqlParameters[7] = new SqlParameter("@username", SqlDbType.NVarChar);
        sqlParameters[7].Value = Convert.ToString(username);
        return da.executeUpdateQuery(query, sqlParameters);
    }
    public bool delStudent(Student s)
    {
        int studentId = s.Id;

        string query = "delete Student where studentId=@studentId";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@studentId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(studentId);

        return da.executeDeleteQuery(query, sqlParameters);
    }
    public DataSet insertCompetition(Competition c)
    {
        string query = "insertCompetition";
        SqlParameter[] sqlParameters = new SqlParameter[11];
        sqlParameters[0] = new SqlParameter("@name", SqlDbType.VarChar);
        sqlParameters[0].Value = Convert.ToString(c.Name);
        sqlParameters[1] = new SqlParameter("@postDate", SqlDbType.VarChar);
        sqlParameters[1].Value = Convert.ToString(c.PostDate);
        sqlParameters[2] = new SqlParameter("@startDate", SqlDbType.VarChar);
        sqlParameters[2].Value = Convert.ToString(c.StartDate);
        sqlParameters[3] = new SqlParameter("@endDate", SqlDbType.VarChar);
        sqlParameters[3].Value = Convert.ToString(c.EndDate);
        sqlParameters[4] = new SqlParameter("@firstPrize", SqlDbType.VarChar);
        sqlParameters[4].Value = Convert.ToString(c.FirstPrize);
        sqlParameters[5] = new SqlParameter("@secondPrize", SqlDbType.VarChar);
        sqlParameters[5].Value = Convert.ToString(c.SecondPrize);
        sqlParameters[6] = new SqlParameter("@thirdPrize", SqlDbType.VarChar);
        sqlParameters[6].Value = Convert.ToString(c.ThirdPrize);
        sqlParameters[7] = new SqlParameter("@description", SqlDbType.VarChar);
        sqlParameters[7].Value = Convert.ToString(c.Description);
        sqlParameters[8] = new SqlParameter("@quotation", SqlDbType.VarChar);
        sqlParameters[8].Value = Convert.ToString(c.Quotation);
        sqlParameters[9] = new SqlParameter("@managerId", SqlDbType.BigInt);
        sqlParameters[9].Value = Convert.ToString(c.Manager.Id);
        sqlParameters[10] = new SqlParameter("@compImage", SqlDbType.VarChar);
        sqlParameters[10].Value = Convert.ToString(c.CompImage);
        return da.executeProcedure(query, sqlParameters);
    }

    public bool updateCompetition(Competition c)
    {
        int comId = c.ComId;
        string name = c.Name;
        string firstPrize = c.FirstPrize;
        string secondPrize = c.SecondPrize;
        string thirdPrize = c.ThirdPrize;
        string postDate = c.PostDate;
        string startDate = c.StartDate;
        string endDate = c.EndDate;
        string description = c.Description;
        string quotation = c.Quotation;

        string query = "update Competition set name=@name,firstPrize=@firstPrize,secondPrize=@secondPrize,thirdPrize=@thirdPrize,postDate=@postDate,startDate=@startDate,endDate=@endDate,description=@description,quotation=@quotation where comId=@comId";

        SqlParameter[] sqlParameters = new SqlParameter[10];
        sqlParameters[0] = new SqlParameter("@name", SqlDbType.NVarChar);
        sqlParameters[0].Value = Convert.ToString(name);
        sqlParameters[1] = new SqlParameter("@firstPrize", SqlDbType.NVarChar);
        sqlParameters[1].Value = Convert.ToString(firstPrize);
        sqlParameters[2] = new SqlParameter("@postDate", SqlDbType.NVarChar);
        sqlParameters[2].Value = Convert.ToString(postDate);
        sqlParameters[3] = new SqlParameter("@startDate", SqlDbType.NVarChar);
        sqlParameters[3].Value = Convert.ToString(startDate);
        sqlParameters[4] = new SqlParameter("@endDate", SqlDbType.NVarChar);
        sqlParameters[4].Value = Convert.ToString(endDate);
        sqlParameters[5] = new SqlParameter("@description", SqlDbType.NVarChar);
        sqlParameters[5].Value = Convert.ToString(description);
        sqlParameters[6] = new SqlParameter("@quotation", SqlDbType.NVarChar);
        sqlParameters[6].Value = Convert.ToString(quotation);
        sqlParameters[7] = new SqlParameter("@comId", SqlDbType.BigInt);
        sqlParameters[7].Value = Convert.ToString(comId);
        sqlParameters[8] = new SqlParameter("@secondPrize", SqlDbType.NVarChar);
        sqlParameters[8].Value = Convert.ToString(secondPrize);
        sqlParameters[9] = new SqlParameter("@thirdPrize", SqlDbType.NVarChar);
        sqlParameters[9].Value = Convert.ToString(thirdPrize);
        return da.executeUpdateQuery(query, sqlParameters);
    }

    public bool delCompetition(int id)
    {
        string query = "delete Competition where comId=@id";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@id", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(id);

        return da.executeDeleteQuery(query, sqlParameters);
    }
    public bool practiceStaffComp(int compId, int staffId)
    {
        string query = "insert into StaffCompetition(staffId,comId) values(@staffId,@comId)";
        SqlParameter[] sqlParameters = new SqlParameter[2];
        sqlParameters[0] = new SqlParameter("@staffId", SqlDbType.Int);
        sqlParameters[0].Value = Convert.ToString(staffId);
        sqlParameters[1] = new SqlParameter("@comId", SqlDbType.Int);
        sqlParameters[1].Value = Convert.ToString(compId);
        return da.executeDeleteQuery(query, sqlParameters);
    }
    public DataSet getAllStudent()
    {
        DataSet ds = new DataSet();
        string query = "select * from Student order by studentId asc";

        SqlParameter[] sqlParameters = new SqlParameter[0];
        ds = da.executeSelectQuery(query, sqlParameters);

        return ds;
    }
    public DataSet getAllCompetition()
    {
        DataSet ds = new DataSet();
        string query = "select c.comId,c.name,c.firstPrize,c.secondPrize,c.thirdPrize,c.postDate,c.startDate,c.endDate,c.[description],c.quotation,m.userName from Competition c inner join Manager m on c.managerId= m.managerId order by c.comId asc";
        SqlParameter[] sqlParameters = new SqlParameter[0];
        ds = da.executeSelectQuery(query, sqlParameters);

        return ds;
    }
    public DataSet searchStudentById(int id)
    {
        DataSet ds = new DataSet();
        string query = "select * from Student where studentId=" + id;
        SqlParameter[] sqlParameters = new SqlParameter[0];
        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }
    public DataSet searchStudentByName(string name)
    {
        DataSet ds = new DataSet();
        string query = "select * from Student where fullName like '%" + name + "%' order by studentId asc";
        SqlParameter[] sqlParameters = new SqlParameter[0];
        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }
    public DataSet searchCompetition(string name)
    {
        DataSet ds = new DataSet();
        string query = "select c.comId,c.name,c.firstPrize,c.secondPrize,c.thirdPrize,c.postDate,c.startDate,c.endDate,c.[description],c.quotation,m.userName,COUNT(s.fullName) as NumberOfStaff from Competition c inner join Manager m on c.managerId= m.managerId inner join StaffCompetition sc on c.comId=sc.comId inner join Staff s on sc.staffId=s.staffId where c.name like '%" + name + "%' group by c.comId,c.name,c.firstPrize,c.secondPrize,c.thirdPrize,c.postDate,c.startDate,c.endDate,c.[description],c.quotation,m.userName order by c.comId asc";
        SqlParameter[] sqlParameters = new SqlParameter[0];
        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }

    public DataSet searchCompetitionById(int comId)
    {
        DataSet ds = new DataSet();
        string query = "select comId,name,firstPrize,secondPrize,thirdPrize,postDate,startDate,endDate,description,quotation,userName,m.managerId,compImage from Competition c inner join Manager m on c.managerId=m.managerId where c.comId=@comId order by c.comId asc";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@comId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(comId);
        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }
    public DataSet getStaffOfComp(int id)
    {
        DataSet ds = new DataSet();
        string query = "select s.staffId,s.fullName from StaffCompetition sc inner join Staff s on sc.staffId=s.staffId where sc.comId="+id;
        SqlParameter[] sqlParameters = new SqlParameter[0];
        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }
    public DataSet searchCompetitionByManager(Manager m)
    {
        int managerId = m.Id;

        DataSet ds = new DataSet();
        string query = "select comId,name,awards,postDate,startDate,endDate,description,quotation,userName from Competition c inner join Manager m on c.managerId=m.managerId where managerId=@managerId order by c.comId asc";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@managerId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(managerId);

        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }

    public bool insertManager(Manager m)
    {
        int managerId = m.Id;
        string userName = m.Username;
        string pass = m.Password;
        string fullName = m.Fullname;
        string address = m.Address;
        string email = m.Email;
        string phoneNo = m.PhoneNumber;

        string query = "insert into Manager values(@managerId,@userName,@pass,@fullName,@address,@email,@phoneNo)";

        SqlParameter[] sqlParameters = new SqlParameter[7];
        sqlParameters[0] = new SqlParameter("@managerId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(managerId);
        sqlParameters[1] = new SqlParameter("@userName", SqlDbType.NVarChar);
        sqlParameters[1].Value = Convert.ToString(userName);
        sqlParameters[2] = new SqlParameter("@pass", SqlDbType.NVarChar);
        sqlParameters[2].Value = Convert.ToString(pass);
        sqlParameters[3] = new SqlParameter("@fullName", SqlDbType.NVarChar);
        sqlParameters[3].Value = Convert.ToString(fullName);
        sqlParameters[4] = new SqlParameter("@address", SqlDbType.NVarChar);
        sqlParameters[4].Value = Convert.ToString(address);
        sqlParameters[5] = new SqlParameter("@email", SqlDbType.NVarChar);
        sqlParameters[5].Value = Convert.ToString(email);
        sqlParameters[6] = new SqlParameter("@phoneNo", SqlDbType.NVarChar);
        sqlParameters[6].Value = Convert.ToString(phoneNo);

        return da.executeInsertQuery(query, sqlParameters);
    }

    public bool updateManager(Manager m)
    {
        int managerId = m.Id;
        string userName = m.Username;
        string pass = m.Password;
        string fullName = m.Fullname;
        string address = m.Address;
        string email = m.Email;
        string phoneNo = m.PhoneNumber;

        string query = "update Manager set pass=@pass,fullName=@fullName,address=@address,email=@email,phoneNo=@phoneNo where managerId=@managerId";

        SqlParameter[] sqlParameters = new SqlParameter[6];
        sqlParameters[0] = new SqlParameter("@managerId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(managerId);
        sqlParameters[1] = new SqlParameter("@pass", SqlDbType.NVarChar);
        sqlParameters[1].Value = Convert.ToString(pass);
        sqlParameters[2] = new SqlParameter("@fullName", SqlDbType.NVarChar);
        sqlParameters[2].Value = Convert.ToString(fullName);
        sqlParameters[3] = new SqlParameter("@address", SqlDbType.NVarChar);
        sqlParameters[3].Value = Convert.ToString(address);
        sqlParameters[4] = new SqlParameter("@email", SqlDbType.NVarChar);
        sqlParameters[4].Value = Convert.ToString(email);
        sqlParameters[5] = new SqlParameter("@phoneNo", SqlDbType.NVarChar);
        sqlParameters[5].Value = Convert.ToString(phoneNo);

        return da.executeUpdateQuery(query, sqlParameters);
    }

    public bool delManager(Manager m)
    {
        int managerId = m.Id;

        string query = "delete Manager where managerId=@managerId";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@managerId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(managerId);

        return da.executeDeleteQuery(query, sqlParameters);
    }

    public DataSet getAllManager()
    {
        DataSet ds = new DataSet();
        string query = "select * from Manager order by userName asc";

        SqlParameter[] sqlParameters = new SqlParameter[0];
        ds = da.executeSelectQuery(query, sqlParameters);

        return ds;
    }

    public DataSet searchManager(string user)
    {
        string query = "select * from Manager where userName=@user order by userName asc";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@user", SqlDbType.NVarChar);
        sqlParameters[0].Value = Convert.ToString(user);

        DataSet ds = new DataSet();
        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }

    public DataSet searchManagerById(int id)
    {
        string query = "select * from Manager where managerId=@id order by userName asc";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@id", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(id);

        DataSet ds = new DataSet();
        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }

    public bool insertStaff(Staff s)
    {
        int staffId = s.Id;
        string userName = s.Username;
        string pass = s.Password;
        string fullName = s.Fullname;
        string address = s.Address;
        string email = s.Email;
        string phoneNo = s.PhoneNumber;
        string major = s.Major;

        string query = "insert into Staff values(@userName,@pass,@fullName,@address,@email,@phoneNo,@major)";

        SqlParameter[] sqlParameters = new SqlParameter[7];
        sqlParameters[0] = new SqlParameter("@userName", SqlDbType.NVarChar);
        sqlParameters[0].Value = Convert.ToString(userName);
        sqlParameters[1] = new SqlParameter("@pass", SqlDbType.NVarChar);
        sqlParameters[1].Value = Convert.ToString(pass);
        sqlParameters[2] = new SqlParameter("@fullName", SqlDbType.NVarChar);
        sqlParameters[2].Value = Convert.ToString(fullName);
        sqlParameters[3] = new SqlParameter("@address", SqlDbType.NVarChar);
        sqlParameters[3].Value = Convert.ToString(address);
        sqlParameters[4] = new SqlParameter("@email", SqlDbType.NVarChar);
        sqlParameters[4].Value = Convert.ToString(email);
        sqlParameters[5] = new SqlParameter("@phoneNo", SqlDbType.NVarChar);
        sqlParameters[5].Value = Convert.ToString(phoneNo);
        sqlParameters[6] = new SqlParameter("@major", SqlDbType.NVarChar);
        sqlParameters[6].Value = Convert.ToString(major);

        return da.executeInsertQuery(query, sqlParameters);
    }

    public bool updateStaff(Staff s)
    {
        int staffId = s.Id;
        string userName = s.Username;
        string pass = s.Password;
        string fullName = s.Fullname;
        string address = s.Address;
        string email = s.Email;
        string phoneNo = s.PhoneNumber;
        string major = s.Major;

        string query = "update Staff set pass=@pass,fullName=@fullName,address=@address,email=@email,phoneNo=@phoneNo,major=@major where staffId=@staffId";

        SqlParameter[] sqlParameters = new SqlParameter[7];
        sqlParameters[0] = new SqlParameter("@staffId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(staffId);
        sqlParameters[1] = new SqlParameter("@pass", SqlDbType.NVarChar);
        sqlParameters[1].Value = Convert.ToString(pass);
        sqlParameters[2] = new SqlParameter("@fullName", SqlDbType.NVarChar);
        sqlParameters[2].Value = Convert.ToString(fullName);
        sqlParameters[3] = new SqlParameter("@address", SqlDbType.NVarChar);
        sqlParameters[3].Value = Convert.ToString(address);
        sqlParameters[4] = new SqlParameter("@email", SqlDbType.NVarChar);
        sqlParameters[4].Value = Convert.ToString(email);
        sqlParameters[5] = new SqlParameter("@phoneNo", SqlDbType.NVarChar);
        sqlParameters[5].Value = Convert.ToString(phoneNo);
        sqlParameters[6] = new SqlParameter("@major", SqlDbType.NVarChar);
        sqlParameters[6].Value = Convert.ToString(major);

        return da.executeUpdateQuery(query, sqlParameters);
    }

    public bool delStaff(Staff s)
    {
        int staffId = s.Id;

        string query = "delete Staff where staffId=@staffId";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@staffId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(staffId);

        return da.executeDeleteQuery(query, sqlParameters);
    }

    public DataSet getAllStaff()
    {
        DataSet ds = new DataSet();
        string query = "select * from Staff order by staffId asc";

        SqlParameter[] sqlParameters = new SqlParameter[0];
        ds = da.executeSelectQuery(query, sqlParameters);

        return ds;
    }

    public DataSet searchStaff(Staff s)
    {
        string userName = s.Username;
        string fullName = s.Fullname;

        DataSet ds = new DataSet();
        string query = "select * from Staff where userName=@userName or fullName=@fullName order by staffId asc";
        SqlParameter[] sqlParameters = new SqlParameter[2];
        sqlParameters[0] = new SqlParameter("@userName", SqlDbType.NVarChar);
        sqlParameters[0].Value = Convert.ToString(userName);
        sqlParameters[1] = new SqlParameter("@fullName", SqlDbType.NVarChar);
        sqlParameters[1].Value = Convert.ToString(fullName);

        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }

    public DataSet searchStaffById(Staff s)
    {
        int staffId = s.Id;

        DataSet ds = new DataSet();
        string query = "select * from Staff where staffId=@staffId order by staffId asc";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@staffId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(staffId);

        ds = da.executeSelectQuery(query, sqlParameters);
        return ds;
    }

    public Staff searchStaffByUser(Staff s)
    {
        string userName = s.Username;

        string query = "select * from Staff where userName=@userName order by staffId asc";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@userName", SqlDbType.NVarChar);
        sqlParameters[0].Value = Convert.ToString(userName);

        DataSet ds = new DataSet();
        ds = da.executeSelectQuery(query, sqlParameters);

        Staff s1 = new Staff();
        DataRow dr = ds.Tables[0].Rows[0];
        s1.Id = Convert.ToInt32(dr[0].ToString());
        s1.Username = dr[1].ToString();
        s1.Password = dr[2].ToString();
        s1.Fullname = dr[3].ToString();
        s1.Address = dr[4].ToString();
        s1.Email = dr[5].ToString();
        s1.PhoneNumber = dr[6].ToString();
        s1.Major = dr[7].ToString();
        return s;
    }

    public bool insertStaffToCompetition(Staff s, Competition c)
    {
        int staffId = s.Id;
        int comId = c.ComId;

        string query = "insert into StaffCompetition values(@staffId,@comId)";

        SqlParameter[] sqlParameters = new SqlParameter[2];
        sqlParameters[0] = new SqlParameter("@staffId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(staffId);
        sqlParameters[1] = new SqlParameter("@comId", SqlDbType.BigInt);
        sqlParameters[1].Value = Convert.ToString(comId);

        return da.executeInsertQuery(query, sqlParameters);
    }

    //updateStaffCompetition chua xong
    public bool updateStaffCompetition(Staff s, Competition c)
    {
        //ko biet duoc id cua StaffCompetition can sua la gi
        /*
        int staffId = s.Id;
        int comId = c.ComId;

        string query = "update StaffCompetition set userName=@userName,pass=@pass,fullName=@fullName,address=@address,email=@email,phoneNo=@phoneNo,major=@major where staffId=@staffId";

        SqlParameter[] sqlParameters = new SqlParameter[2];
        sqlParameters[0] = new SqlParameter("@staffId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(staffId);
        sqlParameters[1] = new SqlParameter("@comId", SqlDbType.BigInt);
        sqlParameters[1].Value = Convert.ToString(comId);

        return da.executeUpdateQuery(query, sqlParameters);
         */
        return true;
    }

    public bool delStaffCompetitionByCom(Competition c)
    {
        int comId = c.ComId;

        string query = "delete StaffCompetition where comId=@comId";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@comId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(comId);

        return da.executeDeleteQuery(query, sqlParameters);
    }

    public bool delStaffCompetitionByStaff(Staff s)
    {
        int staffId = s.Id;

        string query = "delete StaffCompetition where staffId=@staffId";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@staffId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(staffId);

        return da.executeDeleteQuery(query, sqlParameters);
    }

    public DataSet getAllDesignsInCompetition(Competition c)
    {
        int comId = c.ComId;

        string query = "select d.designId,d.name,d.postDate,d.description,d.quotation,st.userName,c.name from Student st inner join Design d on st.studentId=d.studentId inner join Competition c on d.comId=c.comId where comId=@comId order by d.name asc";

        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@comId", SqlDbType.BigInt);
        sqlParameters[0].Value = Convert.ToString(comId);

        return da.executeSelectQuery(query, sqlParameters);
    }
    public double getMarkDesign(int designId)
    {
        DataSet ds = new DataSet();
        string query = "select mark from Mark where designId=@designId";
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sqlParameters[0] = new SqlParameter("@designId", SqlDbType.Int);
        sqlParameters[0].Value = designId;
        ds = da.executeSelectQuery(query, sqlParameters);
        return Convert.ToDouble(ds.Tables[0].Rows[0]["mark"].ToString());
    }
}